<?php
/* explain:		news system
 * Project:     
 * File:        news.php
 *
 * @ link 		http://www.../modules/
 * @ Email		ldmmyx@hotmail.com
 * @ copyright 	2005  Ling Deming
 * @ author 	ivan.ling  Ling Deming
 * @ version 	1.0
 *
 */
include_once("Common.php");
include_once("domain/ImageDomain.php");
include_once("domain/MerchantDomain.php");
include_once("domain/AttributeDomain.php");
include_once("dao/CommonDAO.php");
include_once("dao/MerchantDAO.php");

class MerchantImpl extends Common implements MerchantDAO, CommonDAO
{
	private $conn;
	private $message;
	private $DBTag;

	function MerchantImpl($DBTag)
	{
		parent::Common();
		
		$this->DBTag 	= $DBTag;
		$this->conn 	= $this->getConnection($DBTag);
		
	}

	public function refreshConnetcion()
	{
		$this->freeConnection($this->DBTag, $this->conn);
		
		$this->conn = $this->getConnection($this->DBTag);
	}

	/* get data via ID
	 *
	 * @param  int    id
	 * @return mixed  false:
	 */
	public function getById($id) 
	{
		$sql  = 'SELECT name, nameId, displayURL, descript, isFeatured ';
		$sql .= 'FROM merchant WHERE id='.$id;
		
		try {
			$rs = mysql_query($sql, $this->conn);
			if ($row = mysql_fetch_row($rs)) {
				$oMerchantDomain = new MerchantDomain();

				$oMerchantDomain->setId($id);
				$oMerchantDomain->setName($row[0]);
				$oMerchantDomain->setNameId($row[1]);
				$oMerchantDomain->setDisplayURL($row[2]);
				$oMerchantDomain->setDescript($row[3]);
				$oMerchantDomain->setFeatured($row[4]);
				
				return $oMerchantDomain;
			} else {
				$this->message = "get merchant data failed by Id. sql : $sql <br>";
				$this->message .= "error: " . mysql_error($this->conn);

				return null;
			}
		} catch (Exception $e) {
			$this->message = $e->getMessage();

			return null;
		}
	}

	/* get data via ID
	 *
	 * @param  int    id
	 * @return mixed  false:
	 */
	public function getByNameId($id) 
	{
		$sql  = 'SELECT id, name, nameId, displayURL, descript, isFeatured ';
		$sql .= "FROM merchant WHERE nameId='$id'";
		
		try {
			$rs = mysql_query($sql, $this->conn);
			if ($rs) {
				$row = mysql_fetch_row($rs);

				$oMerchantDomain = new MerchantDomain();

				$oMerchantDomain->setId($row[0]);
				$oMerchantDomain->setName($row[1]);
				$oMerchantDomain->setNameId($row[2]);
				$oMerchantDomain->setDisplayURL($row[3]);
				$oMerchantDomain->setDescript($row[4]);
				$oMerchantDomain->setFeatured($row[5]);
				
				return $oMerchantDomain;
			} else {
				$this->message = "get merchant data failed by Id. sql : $sql <br>";
				$this->message .= "error: " . mysql_error($this->conn);

				return null;
			}
		} catch (Exception $e) {
			$this->message = $e->getMessage();

			return null;
		}
	}
	
	public function getByAlias($alias) 
	{
		$sql = "SELECT merchantId FROM merchant_alias WHERE alias='" . addslashes($alias) . "'";
		
		$mercahntId = $this->getColumn($this->DBTag, $sql);
		
		if(empty($mercahntId)){
			return false;
		}else{
			return $this->getById($mercahntId);
		}
	}
	
	/* get merchant object via advertiser Id
	 *
	 * @param  int    id
	 * @return mixed  false:
	 */
	public function getAdvertiserCategory($sourceId, $advertiserId) 
	{
		$sql  	= "SELECT category FROM advertiser_category_log WHERE advertiserId = '$advertiserId' AND sourceId = '$sourceId'";
		
		return $this->getColumn($this->DBTag, $sql);
	}
	
	public function getMerchantByAdvertiserId($advertiserId) 
	{
		$sql  		= "SELECT ma.merchantId FROM merchant_attributes ma WHERE ma.attributeId = 5 AND ma.attributeValue = '$advertiserId'";
		
		$merchantId = $this->getColumn($this->DBTag, $sql);
		
		if(!empty($merchantId)){
			return $this->getById($merchantId);
		}else{
			return false;
		}
	}
	
	public function add($oMerchantDomain)
	{		
		if($oMerchantDomain instanceof MerchantDomain){
			$sql 	 = "INSERT INTO merchant SET createdTime = UTC_TIMESTAMP(), descript='";
			$sql	.= addslashes($oMerchantDomain->getDescript());
			
			$sql 	.= "', name='";
			$sql	.= addslashes($oMerchantDomain->getName());
			
			$sql 	.= "', nameId='";
			$sql	.= addslashes($oMerchantDomain->getNameId());	
	
			$sql 	.= "', displayURL='";
			$sql	.= addslashes($oMerchantDomain->getdisplayURL());
	
			$sql 	.= "', trackingURL='";
			$sql	.= $oMerchantDomain->getTrackingURL();

			$sql	.= "', CSPhone='";
			$sql	.= $oMerchantDomain->getCSPhone();

			$sql	.= "', CSEmail='";
			$sql	.= $oMerchantDomain->getCSEmail();

			$sql	.= "', status='";
			$sql	.= $oMerchantDomain->getStatus();
			
			$sql	.= "', r_nameFirstLetter='";
			$sql	.= $oMerchantDomain->getNameFirstLetter();
			
			$sql	.= "' ON DUPLICATE KEY UPDATE trackingURL=Values(trackingURL), displayURL=Values(displayURL), CSPhone=Values(CSPhone), " .
					"CSEmail=Values(CSEmail), descript=Values(descript), r_nameFirstLetter=Values(r_nameFirstLetter)";
			
			$id		= 0;
			
			try{
				$rs = mysql_query($sql, $this->conn);
				if($rs){
					$id = mysql_insert_id($this->conn);
					
					$oMerchantDomain->setId($id);
					
					$this->message = "add successfully. id = $id";
				}else{
					$this->message  = "add failed. sql : $sql . <br>";
					$this->message .= "error: " .  mysql_error($this->conn);
				}
			}catch(Exception $e){
				$this->message = "add failed. sql : $sql . \n, mysql: ";
				$this->message .= $e->getMessage();
				
				$id	= -1;
			}
		}else{
			$id	= -1;
			
			$this->message = 'input object is not instance of MerchantDomain.';
		}
			
		return $id;
	}
	
	public function update($oMerchantDomain)
	{		
		$id	= 0;
		
		if($oMerchantDomain instanceof MerchantDomain){
			$sql 	 = "UPDATE merchant SET createdTime = UTC_TIMESTAMP(), descript='";
			$sql	.= addslashes($oMerchantDomain->getDescript());
			
			$sql 	.= "', name='";
			$sql	.= addslashes($oMerchantDomain->getName());
			
			$sql 	.= "', nameId='";
			$sql	.= addslashes($oMerchantDomain->getNameId());	
	
			$sql 	.= "', displayURL='";
			$sql	.= addslashes($oMerchantDomain->getdisplayURL());
	
			$sql 	.= "', trackingURL='";
			$sql	.= $oMerchantDomain->getTrackingURL();

			$sql	.= "', CSPhone='";
			$sql	.= $oMerchantDomain->getCSPhone();

			$sql	.= "', CSEmail='";
			$sql	.= $oMerchantDomain->getCSEmail();

			$sql	.= "', status='";
			$sql	.= $oMerchantDomain->getStatus();
			
			$sql	.= "', r_nameFirstLetter='";
			$sql	.= $oMerchantDomain->getNameFirstLetter();
			
			$sql	.= "' WHERE id = ";
			$sql	.= $oMerchantDomain->getId();
			
			try{
				$rs = mysql_query($sql, $this->conn);
				if($rs){
					$id	= 1;
					 				
					$this->message = "update successfully. id = $id";
				}else{
					$this->message  = "update failed. sql : $sql . <br>";
					$this->message .= "error: " .  mysql_error($this->conn);
				}
			}catch(Exception $e){
				$this->message = "update failed. sql : $sql . \r, mysql: ";
				$this->message .= $e->getMessage();
				
				$id	= -1;
			}
		}else{
			$id	= -1;
			
			$this->message = 'input object is not instance of MerchantDomain.';
		}
			
		return $id;
	}
	
	public function addAttribute($sourceId, $merchantId, $attributeId, $attributeValue)
	{
		$sql_data_array = array(
			'merchantId' 		=> $merchantId,
			'sourceId' 			=> $sourceId,
			'attributeId' 		=> $attributeId,
			'attributeValue' 	=> $attributeValue
		);

		return $this->insertUpdate(DB_TAG_COUPON, 'merchant_attributes', $sql_data_array, 'insert', " ON DUPLICATE KEY UPDATE sourceId=Values(sourceId), attributeValue=Values(attributeValue), modifiedTime=Values(modifiedTime)");
	}
	
	public function addAlias($merchantId, $merchantName)
	{
		$sql_data_array = array(
			'alias' 	=> $merchantName,
			'merchantId' 	=> $merchantId
		);
				
		$this->insertUpdate($this->DBTag, 'merchant_alias', $sql_data_array, 'insert', " ON DUPLICATE KEY UPDATE merchantId=Values(merchantId)");
	}
	
	public function addAdvertiserCategory($sourceId, $advertiserId, $category)
	{
		$sql_data_array = array(
			'advertiserId' 	=> $advertiserId,
			'sourceId' 	=> $sourceId,
			'category' 	=> $category
		);
				
		$this->insertUpdate($this->DBTag, 'advertiser_category_log', $sql_data_array, 'insert', " ON DUPLICATE KEY UPDATE category=Values(category)");
	}
	
	public function addLogo($merchantId, $imageId, $level = 0)
	{
		$sql_data_array = array(
			'merchantId' 	=> $merchantId,
			'imageId' 		=> $imageId,
			'level' 		=> $level,
		);
				
		$this->insertUpdate($this->DBTag, 'merchant_images', $sql_data_array, 'insert', " ON DUPLICATE KEY UPDATE level=Values(level)");
	}
	
	public function addCategory($merchantId, $categoryId)
	{
		$sql_data_array = array(
			'merchantId' 	=> $merchantId,
			'categoryId' 	=> $categoryId
			
		);
				
		$this->insertUpdate($this->DBTag, 'merchant_category', $sql_data_array, 'insert', " ON DUPLICATE KEY UPDATE merchantId=Values(merchantId)");
	}
	
	/* delete news class
	 *
	 * @param  int    	id
	 * @param  string  	status
	 * @param  return 	boolean
	 */
	public function delete($id)
	{
		$sql = "DELETE FROM merchant WHERE id = $id";
		
		$rs	 = mysql_query($sql, $this->conn) or die (mysql_error());
		if($rs)
			return true;
		else
			return false;
	}
	
	public function getPopular($page = 0, $discount = 20)
	{
		
		$where	= ' WHERE status = 1';
		$order 	= ' ORDER BY r_popularity DESC';
		
		$oMerchants = $this->getCustoms($where, $order, $page, $discount);
		
		foreach($oMerchants as $oMerchant){
			$this->getAttributes($oMerchant);
			
//			echo "attriubtes : " . $oMerchant->getAttribute(1, 52)->getAttributeValue() . "<br>";
		}
		
		return $oMerchants;
	}
	
	public function getPopularByRelatedCategory($merId, $page = 0, $discount = 20)
	{
		$sql 		= "SELECT GROUP_CONCAT(categoryId) FROM merchant_category WHERE merchantId = $merId GROUP BY merchantId";
		$catIdList 	= $this->getColumn($this->DBTag, $sql);
		
		if(empty($catIdList)){
			return array();
		}else{
			$where	= " WHERE status = 1 AND id in (SELECT merchantId FROM merchant_category WHERE categoryId IN ($catIdList))";
			$order 	= ' ORDER BY r_popularity DESC';
			
			return $this->getCustoms($where, $order, $page, $discount);			
		}
	}
	
	public function getPopularByCategoryId($categoryId, $page = 0, $discount = 20)
	{
		$where	= " WHERE status = 1 AND id in (SELECT merchantId FROM merchant_category WHERE categoryId = $categoryId)";
		$order 	= ' ORDER BY r_popularity DESC';
		
		return $this->getCustoms($where, $order, $page, $discount);
	}
	
	public function getPopularByTagId($tagId, $page = 0, $discount = 20)
	{
		$where	= " WHERE status = 1 AND id in (SELECT merchantId FROM merchant_tag WHERE authorized = 1 AND tagId IN ($tagId))";
		$order 	= ' ORDER BY r_popularity DESC';
		
		return $this->getCustoms($where, $order, $page, $discount);
	}
	
	public function getFeaturedMerchants($firstLetter, $page = 0, $discount = 20)
	{
		$where	= " WHERE status = 1 AND isFeatured = 1 AND r_nameFirstLetter = '$firstLetter'";
		$order 	= '';
		
		return $this->getCustoms($where, $order, $page, $discount);
	}
	
	public function getAllFeaturedMerchants($page = 0, $discount = 20)
	{
		$where	= ' WHERE status = 1 AND isFeatured = 1';
		$order 	= '';
		
		return $this->getCustoms($where, $order, $page, $discount);
	}
	
	public function getMerchantsByFirstLetter($firstLetter)
	{
		$where	= " WHERE status = 1 AND r_CouponCount > 0 AND r_nameFirstLetter = '$firstLetter'";
		$order 	= ' ORDER BY name';
		
		return $this->getCustoms($where, $order, 0, 0);
	}

	/* merchant list
	 *
	 * @ param  return 	array
	 */

	public function getValidAllMerchants()
	{
		$where	= ' WHERE status = 1 AND r_CouponCount > 0';
		$order 	= '';
		
		return $this->getCustoms($where, $order, 0, 0);
	}
	
	/* list
	 *
	 * @ param  string 	class id
	 * @ param  string 	page
	 * @ param  string 	discount
	 * @ param  string 	url
	 * @ param  return 	array
	 */
	public function getCustoms($where, $order, $page = 0, $discount = 20)
	{
		$arrback = array();
		
		try{
			$sql 	= 'SELECT count(1) FROM merchant' . $where;
			
			$rs  	= mysql_query($sql, $this->conn);
			
			list($total) = mysql_fetch_row($rs);
			
			$this->mutilPager($total, $page, $discount);
			
			if(!empty($total)){
				$sql  = 'SELECT id, name, nameId, displayURL, descript, r_CouponCount, isFeatured ';
				$sql .= "FROM merchant $where $order";
				
				if($discount > 0)
					$sql .= " LIMIT $this->firstcount, $discount";
				
				$rs  = mysql_query($sql, $this->conn);
				if($rs){
					while($row = mysql_fetch_array($rs)){
						$oMerchantDomain = new MerchantDomain();

						$oMerchantDomain->setId($row[0]);
						$oMerchantDomain->setName($row[1]);
						$oMerchantDomain->setNameId($row[2]);
						$oMerchantDomain->setDisplayURL($row[3]);
						$oMerchantDomain->setDescript($row[4]);
						$oMerchantDomain->setCouponCount($row[5]);
						$oMerchantDomain->setFeatured($row[6]);
						
						$arrback[] = $oMerchantDomain;
					}
				}else {
					$this->message = "select failed. sql : $sql";
					$this->message .= "<br>error: " . mysql_error($this->conn);
				}
			}
		}catch(Exception $e){
			$this->message = $e->getMessage();
			
			return null;
		}
		
		return $arrback;
	}
	
	public function getCouponCount()
	{
		$sql  = 'SELECT merchantId, COUNT(1) total FROM coupon WHERE status = 1 GROUP BY merchantId';
			
		return $this->getHash($this->DBTag, $sql);
	}
	
	public function getHasCoupons()
	{
		$sql  = 'SELECT id, r_couponCount FROM merchant WHERE status = 1 AND r_couponCount > 0';
			
		return $this->getHash($this->DBTag, $sql);
	}
	
	public function getLogo($mercahntId) 
	{
		$sql  = "SELECT b.id id, b.extName extName FROM merchant_images a, upload_files b WHERE a.imageID = b.id AND a.merchantID = $mercahntId";
		$sql .= " ORDER BY a.level LIMIT 1";

		try {
			$rs = mysql_query($sql, $this->conn);
			if ($row = mysql_fetch_row($rs)) {
				$oImage = new ImageDomain();

				$oImage->setId($row[0]);
				$oImage->setExtName($row[1]);
	
				return $oImage;
			} else {
				$this->message = "get merchant logo data failed by Id. sql : $sql <br>";
				$this->message .= "error: " . mysql_error($this->conn);

				return null;
			}
		} catch (Exception $e) {
			$this->message = $e->getMessage();

			return null;
		}
	}
	
	public function getSiteThumbnail($mercahntId) 
	{
		$sql  = "SELECT b.id id, b.extName extName FROM site_images a, site_files b WHERE a.imageID = b.id AND a.merchantID = $mercahntId";
		$sql .= " ORDER BY a.level LIMIT 1";
		
		try {
			$rs = mysql_query($sql, $this->conn);
			if ($row = mysql_fetch_row($rs)) {
				$oImage = new ImageDomain();

				$oImage->setId($row[0]);
				$oImage->setExtName($row[1]);
				
				return $oImage;
			} else {
				$this->message = "get merchant logo data failed by Id. sql : $sql <br>";
				$this->message .= "error: " . mysql_error($this->conn);

				return null;
			}
		} catch (Exception $e) {
			$this->message = $e->getMessage();

			return null;
		}
	}
	
	public function getAttributes($oMerchant)
	{
		$arrback = array();
		
		$sql  = "SELECT sourceId, attributeId, attributeValue FROM merchant_attributes WHERE merchantId = " . $oMerchant->getId();
		
		try {
			$rs = mysql_query($sql, $this->conn);
			while ($row = mysql_fetch_row($rs)) {
				$oAttribute = new AttributeDomain();

				$oAttribute->setSourceId($row[0]);
				$oAttribute->setAttributeId($row[1]);
				$oAttribute->setAttributeValue($row[2]);
				
				$arrback[$row[0] . '_' . $row[1]] = $oAttribute;
			}
			
			$oMerchant->setAttributes($arrback);
		} catch (Exception $e) {
			$this->message = $e->getMessage();

			return null;
		}
		
		return $arrback;
	}
	
	public function getSources($mercahntId)
	{
		$arrback = array();
		
		$sql  = "SELECT sourceId FROM merchant_source WHERE merchantId = $mercahntId";
		
		try {
			$rs = mysql_query($sql, $this->conn);
			while ($row = mysql_fetch_row($rs)) {
				$arrback[$row[0]] = $row[0];
			}
		} catch (Exception $e) {
			$this->message = $e->getMessage();

			return null;
		}
		
		return $arrback;
	}
	
	public function getCategories($mercahntId)
	{
		$arrback = array(); 
		
		$sql  = "SELECT categoryId FROM merchant_category WHERE merchantId = $mercahntId";
		
		try {
			$rs = mysql_query($sql, $this->conn);
			while ($row = mysql_fetch_row($rs)) {
				$arrback[$row[0]] = $row[0];
			}
		} catch (Exception $e) {
			$this->message = $e->getMessage();

			return null;
		}
		
		return $arrback;
	}
	
	
	
	public function getMessage() 
	{
		return $this->message;
	}

	
	
}
?>
